/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package dao;

import bean.Account;
import bean.Category;
import bean.Complaint;
import bean.Deparment;
import bean.Priority;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import utility.DBUtility;

/**
 *
 * @author Administrator
 */
public class ReportAccess {

    public static List<Complaint> getAllComplaint() {
        List<Complaint> list = new ArrayList<Complaint>();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {

            conn = DBUtility.getConnect();
            ps = conn.prepareStatement("Select * from complaint");
            rs = ps.executeQuery();
            while (rs.next()) {
                list.add(new Complaint(
                        rs.getInt("id"),
                        rs.getInt("acc_id"),
                        rs.getByte("cate_id"),
                        rs.getInt("dep_id"),
                        rs.getInt("tech_id"),
                        rs.getInt("pri_id"),
                        rs.getString("title"),
                        rs.getString("description"),
                        rs.getDate("date_open"),
                        rs.getDate("date_close"),
                        rs.getDate("create_time"),
                        rs.getDate("update_time"),
                        rs.getString("cusEmail")));
            }

        } catch (SQLException ex) {
            Logger.getLogger(ReportAccess.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            DBUtility.closeAll(conn, ps, rs);
        }

        return list;
    }

    public static List<Category> getAllCategory() {
        List<Category> list = new ArrayList<Category>();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {

            conn = DBUtility.getConnect();
            ps = conn.prepareStatement("Select * from category");
            rs = ps.executeQuery();
            while (rs.next()) {
                list.add(new Category(
                        rs.getInt("id"),
                        rs.getString("cate_name"),
                        rs.getInt("status"),
                        rs.getDate("create_time"),
                        rs.getDate("update_time")));
            }

        } catch (SQLException ex) {
            Logger.getLogger(ReportAccess.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            DBUtility.closeAll(conn, ps, rs);
        }

        return list;
    }

    public static List<Deparment> getAllDeparment() {
        List<Deparment> list = new ArrayList<Deparment>();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {

            conn = DBUtility.getConnect();
            ps = conn.prepareStatement("Select * from Deparment");
            rs = ps.executeQuery();
            while (rs.next()) {
                list.add(new Deparment(
                        rs.getInt("id"),
                        rs.getString("dep_name"),
                        rs.getInt("status"),
                        rs.getDate("create_time"),
                        rs.getDate("update_time")));
            }

        } catch (SQLException ex) {
            Logger.getLogger(ReportAccess.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            DBUtility.closeAll(conn, ps, rs);
        }

        return list;
    }

    public static List<Priority> getAllPriority() {
        List<Priority> list = new ArrayList<Priority>();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {

            conn = DBUtility.getConnect();
            ps = conn.prepareStatement("Select * from Priority");
            rs = ps.executeQuery();
            while (rs.next()) {
                list.add(new Priority(
                        rs.getInt("id"),
                        rs.getString("pri_name"),
                        rs.getInt("status"),
                        rs.getDate("create_time"),
                        rs.getDate("update_time")));
            }

        } catch (SQLException ex) {
            Logger.getLogger(ReportAccess.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            DBUtility.closeAll(conn, ps, rs);
        }

        return list;
    }

    public static Account findAcc(int accId) {
        Account a = null;
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {

            conn = DBUtility.getConnect();
            ps = conn.prepareStatement("Select * from Account where id = ?");
            ps.setInt(1, accId);
            rs = ps.executeQuery();
            if (rs.next()) {
                a = new Account(
                        accId, rs.getInt("dep_id"),
                        rs.getInt("role_id"),
                        rs.getString("account"),
                        rs.getString("password"),
                        rs.getString("fullname"),
                        rs.getString("email"),
                        rs.getString("phone"),
                        rs.getString("address"),
                        rs.getInt("status"),
                        rs.getDate("create_time"),
                        rs.getDate("update_time"));

            }

        } catch (SQLException ex) {
            Logger.getLogger(ReportAccess.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            DBUtility.closeAll(conn, ps, rs);
        }
        return a;
    }

    public static Category findCate(int cateId) {
        Category cate = null;
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {

            conn = DBUtility.getConnect();
            ps = conn.prepareStatement("Select * from category where id = ?");
            ps.setInt(1, cateId);
            rs = ps.executeQuery();
            if (rs.next()) {
                cate = new Category(cateId,
                        rs.getString("cate_name"),
                        rs.getInt("status"),
                        rs.getDate("create_time"),
                        rs.getDate("update_time"));
            }

        } catch (SQLException ex) {
            Logger.getLogger(ReportAccess.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            DBUtility.closeAll(conn, ps, rs);
        }

        return cate;
    }

    public static Deparment findDep(int depId) {
        Deparment dep = null;
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {

            conn = DBUtility.getConnect();
            ps = conn.prepareStatement("Select * from deparment where id = ?");
            ps.setInt(1, depId);
            rs = ps.executeQuery();
            if (rs.next()) {
                dep = new Deparment(
                        depId,
                        rs.getString("dep_name"),
                        rs.getInt("status"),
                        rs.getDate("create_time"),
                        rs.getDate("update_time"));
            }

        } catch (SQLException ex) {
            Logger.getLogger(ReportAccess.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            DBUtility.closeAll(conn, ps, rs);
        }
        return dep;

    }

    public static Priority findPri(int priId) {
        Priority pri = null;
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {

            conn = DBUtility.getConnect();
            ps = conn.prepareStatement("Select * from priority where id = ?");
            ps.setInt(1, priId);
            rs = ps.executeQuery();
            if (rs.next()) {
                pri = new Priority(
                        priId,
                        rs.getString("pri_name"),
                        rs.getInt("status"),
                        rs.getDate("create_time"),
                        rs.getDate("update_time"));
            }

        } catch (SQLException ex) {
            Logger.getLogger(ReportAccess.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            DBUtility.closeAll(conn, ps, rs);
        }
        return pri;
    }

    public static void main(String[] args) {
//        System.out.println("Detailed report");
//        System.out.println("----------------------");
//        List<Complaint> list = getAllComplaint();
//        for (Complaint l : list) {
//            System.out.println("Complaint id: " + l.getId());
//            System.out.println("Category: " + findCate(l.getCateId()).getCateName());
//            if (l.getDateOpen() != null) {
//                System.out.println("Date of lodging complaint: " + l.getDateOpen());
//            } else {
//                System.out.println("Date of lodging complaint: Not yet");
//            }
//
//            if (l.getDateClose() != null) {
//                System.out.println("Date of closing complaint: " + l.getDateClose());
//            } else {
//                System.out.println("Date of closing complaint: Not yet ");
//            }
//            System.out.println("Department of the user: " + findDep(findAcc(l.getAccId()).getDepId()).getDepName());
//            if (findAcc(l.getTechId()) != null) {
//                System.out.println("Name of the technician: " + findAcc(l.getTechId()).getFullName());
//            } else {
//                System.out.println("Name of the technician: Not yet");
//            }
//            if (l.getDateClose() != null) {
//                long day = (l.getDateClose().getTime() - l.getDateOpen().getTime()) / (24 * 3600 * 1000);
//                long time = ((l.getDateClose().getTime() - l.getDateOpen().getTime()) % (24 * 3600 * 1000)) / 1000;
//                long gio = time / (3600);
//                time = time % (3600);
//                long fut = time / (60);
//                time = time % (60);
//                long giay = time;
//                System.out.println("Actual time taken to solve the problem: " + day + " days : " + gio + " Hr : " + fut + " Min : " + giay + " Sec");
//            } else {
//                System.out.println("Actual time taken to solve the problem: Not yet");
//            }
//            System.out.println("Employee no/name who registered the complaint: " + findAcc(l.getAccId()).getId() + "/" + findAcc(l.getAccId()).getFullName());
//
//            System.out.println("---------------------");
//        }

//        System.out.println(addNewComplaint(new Complaint(0, 6, 2, 12, 0, 2, "Title", "Some thingsassdas", new Date(), "asd@asd.asd")));
//        for (Account a : getAllAccount(4)) {
//            System.out.println(a);
//
//        }

    }

    public static String addNewComplaint(Complaint comp) {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            conn = DBUtility.getConnect();
            ps = conn.prepareStatement("insert into complaint (acc_id,cate_id,dep_id,pri_id,title,description,create_time,cusEmail) values(?,?,?,?,?,?,?,?)");
            ps.setInt(1, comp.getAccId());
            ps.setInt(2, comp.getCateId());
            ps.setInt(3, comp.getDepId());
            ps.setInt(4, comp.getPriId());
            ps.setString(5, comp.getTitle());
            ps.setString(6, comp.getDescription());
            ps.setDate(7, new java.sql.Date(comp.getCreateTime().getTime()));
            ps.setString(8, comp.getCusEmail());
            System.out.println(comp.getDepId() + "");
            int add = ps.executeUpdate();
            if (add > 0) {
                return "ok";
            } else {
                return "fail";
            }

        } catch (SQLException ex) {
            Logger.getLogger(ReportAccess.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            DBUtility.closeAll(conn, ps, rs);
        }
        return "fail";

    }

    public static List<Complaint> getAllComplaintByCateId(int cateId) {
        List<Complaint> list = new ArrayList<Complaint>();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {

            conn = DBUtility.getConnect();
            ps = conn.prepareStatement("Select * from complaint where cate_id = ?");
            ps.setInt(1, cateId);
            rs = ps.executeQuery();
            while (rs.next()) {
                list.add(new Complaint(
                        rs.getInt("id"),
                        rs.getInt("acc_id"),
                        rs.getByte("cate_id"),
                        rs.getInt("dep_id"),
                        rs.getInt("tech_id"),
                        rs.getInt("pri_id"),
                        rs.getString("title"),
                        rs.getString("description"),
                        rs.getDate("date_open"),
                        rs.getDate("date_close"),
                        rs.getDate("create_time"),
                        rs.getDate("update_time"),
                        rs.getString("cusEmail")));
            }

        } catch (SQLException ex) {
            Logger.getLogger(ReportAccess.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            DBUtility.closeAll(conn, ps, rs);
        }

        return list;
    }

    public static List<Complaint> getAllComplaintByTechId(int techId) {
        List<Complaint> list = new ArrayList<Complaint>();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {

            conn = DBUtility.getConnect();
            ps = conn.prepareStatement("Select * from complaint where tech_id = ?");
            ps.setInt(1, techId);
            rs = ps.executeQuery();
            while (rs.next()) {
                list.add(new Complaint(
                        rs.getInt("id"),
                        rs.getInt("acc_id"),
                        rs.getByte("cate_id"),
                        rs.getInt("dep_id"),
                        rs.getInt("tech_id"),
                        rs.getInt("pri_id"),
                        rs.getString("title"),
                        rs.getString("description"),
                        rs.getDate("date_open"),
                        rs.getDate("date_close"),
                        rs.getDate("create_time"),
                        rs.getDate("update_time"),
                        rs.getString("cusEmail")));
            }

        } catch (SQLException ex) {
            Logger.getLogger(ReportAccess.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            DBUtility.closeAll(conn, ps, rs);
        }

        return list;
    }

    public static List<Complaint> getAllComplaintByDepId(int depId) {
        List<Complaint> list = new ArrayList<Complaint>();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {

            conn = DBUtility.getConnect();
            ps = conn.prepareStatement("Select * from complaint where dep_id = ?");
            ps.setInt(1, depId);
            rs = ps.executeQuery();
            while (rs.next()) {
                list.add(new Complaint(
                        rs.getInt("id"),
                        rs.getInt("acc_id"),
                        rs.getByte("cate_id"),
                        rs.getInt("dep_id"),
                        rs.getInt("tech_id"),
                        rs.getInt("pri_id"),
                        rs.getString("title"),
                        rs.getString("description"),
                        rs.getDate("date_open"),
                        rs.getDate("date_close"),
                        rs.getDate("create_time"),
                        rs.getDate("update_time"),
                        rs.getString("cusEmail")));
            }

        } catch (SQLException ex) {
            Logger.getLogger(ReportAccess.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            DBUtility.closeAll(conn, ps, rs);
        }

        return list;
    }

    public static List<Account> getAllAccount(int roleId) {
        List<Account> list = new ArrayList<Account>();
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {

            conn = DBUtility.getConnect();
            ps = conn.prepareStatement("Select * from Account where role_id = ?");
            ps.setInt(1, roleId);
            rs = ps.executeQuery();
            while (rs.next()) {
                list.add(new Account(
                        rs.getInt("id"),
                        rs.getInt("dep_id"),
                        rs.getInt("role_id"),
                        rs.getString("account"),
                        rs.getString("password"),
                        rs.getString("fullname"),
                        rs.getString("email"),
                        rs.getString("phone"),
                        rs.getString("address"),
                        rs.getInt("status"),
                        rs.getDate("create_time"),
                        rs.getDate("update_time")));
            }

        } catch (SQLException ex) {
            Logger.getLogger(ReportAccess.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            DBUtility.closeAll(conn, ps, rs);
        }

        return list;

    }
}
